package at.technikum.fotoalbum.database;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Vector;

import at.technikum.fotoalbum.beans.*;


public class DatabaseConnector {

	private static String database_url = "jdbc:mysql://wp132.webpack.hosteurope.de/db1095623-bilderalbum";
	//private static String database = "db1095623-bilderalbum";
	private static String database_user = "db1095623-album";
	private static String database_password = "technikum";
	public static Connection dbconnection;
	//private static final String TAG = DatabaseConnector.class.getSimpleName();
	
	// Database structure
	
	private static String KEY_TABLE_USER = "User";
	private static String KEY_TABLE_USER_ID = "user_ID";
	private static String KEY_TABLE_USER_USERNAME = "username";
	private static String KEY_TABLE_USER_PASSWORD = "password";
	private static String KEY_TABLE_USER_FIRSTNAME = "firstname";
	private static String KEY_TABLE_USER_LASTNAME = "lastname";
	private static String KEY_TABLE_USER_EMAIL = "email";
	private static String KEY_TABLE_USER_ISADMIN = "isadmin";
	private static String KEY_TABLE_USER_ISCONFIRMED = "isconfirmed";
	
	private static String KEY_TABLE_IMAGE = "Image";
	private static String KEY_TABLE_IMAGE_ID = "image_ID";
	private static String KEY_TABLE_IMAGE_NAME = "imagename";
	private static String KEY_TABLE_IMAGE_FILENAME = "filename";
	private static String KEY_TABLE_IMAGE_PATH = "path";
	private static String KEY_TABLE_IMAGE_DATE = "date";
	private static String KEY_TABLE_IMAGE_USERNAME = "username";
	
	
	public DatabaseConnector () {
		try {
			LoadDatabaseDriver.main(null);
		} catch (Exception ex) {
		    System.out.println("SQLException: " + ex.getMessage());
		}
		
		/*
		try {
			dbconnection = DriverManager.getConnection(database_url,database_user,database_password);
			System.out.println("DriverManager.getLoginTimeout(): " + DriverManager.getLoginTimeout());
		} catch (SQLException ex) {
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		}
		*/		
	}
		
	public static void main(String[] args) {
		// LoadDatabaseDriver.main(null);
	}
	
	public static Connection EstablishDBConnection () {
		try {
			dbconnection = DriverManager.getConnection(database_url,database_user,database_password);
		} catch (SQLException ex) {
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		}
		return dbconnection;
	}
	
	public void closeDBConnection() {
		try {
			dbconnection.close();
		} catch (SQLException ex) {
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		}		
	}
	/*
	 * Returns an ArrayList<Person> of all users in the database
	 */
	public ArrayList<Person> getAllUsers() {
		ArrayList<Person> userarray = new ArrayList<Person>();
		EstablishDBConnection();
		try {
			Statement stm = dbconnection.createStatement();
			String sql ="SELECT * FROM " + KEY_TABLE_USER;
			ResultSet result = stm.executeQuery(sql);
	          while(result.next() == true) {
	        	  Person user = new Person();
	        	  user.setId(result.getLong(KEY_TABLE_USER_ID));
	        	  user.setNick(result.getString(KEY_TABLE_USER_USERNAME));
	        	  user.setPassword(result.getString(KEY_TABLE_USER_PASSWORD));
	        	  user.setVorname(result.getString(KEY_TABLE_USER_FIRSTNAME));
	        	  user.setNachname(result.getString(KEY_TABLE_USER_LASTNAME));
	        	  user.setEmail(result.getString(KEY_TABLE_USER_EMAIL));
	        	  user.setAdmin(result.getBoolean(KEY_TABLE_USER_ISADMIN));
	        	  user.setConfirmed(result.getBoolean(KEY_TABLE_USER_ISCONFIRMED));
	        	  userarray.add(user);
	          }
		}
		catch (Exception e) {
	          System.out.println("Exception " + e);
	    }
		finally {
			closeDBConnection();
		}
		closeDBConnection();
		return userarray;
	}
	
	public Person getUserFromUsername(String username) {
		Person user = new Person();
		EstablishDBConnection();
		try {
			// TODO: Prepared Statement
			Statement stm = dbconnection.createStatement();
			String sql ="SELECT * FROM " + KEY_TABLE_USER + " WHERE " + KEY_TABLE_USER_USERNAME + " = '" + username + "'";
			ResultSet result = stm.executeQuery(sql);
	          while(result.next() == true) {
	        	  user.setId(result.getLong(KEY_TABLE_USER_ID));
	        	  user.setNick(result.getString(KEY_TABLE_USER_USERNAME));
	        	  user.setPassword(result.getString(KEY_TABLE_USER_PASSWORD));
	        	  user.setVorname(result.getString(KEY_TABLE_USER_FIRSTNAME));
	        	  user.setNachname(result.getString(KEY_TABLE_USER_LASTNAME));
	        	  user.setEmail(result.getString(KEY_TABLE_USER_EMAIL));
	        	  user.setAdmin(result.getBoolean(KEY_TABLE_USER_ISADMIN));
	        	  user.setConfirmed(result.getBoolean(KEY_TABLE_USER_ISCONFIRMED));
	          }
		}
		catch (Exception e) {
	          System.out.println("Exception " + e);
	    }
		finally {
			closeDBConnection();
		}
		closeDBConnection();
		return user;
	}
	
	/*
	 * @param user: user to be created in the database
	 * 
	 * parameters isadmin and isconformed from Person user are ignored and will be set automatically to false in the database
	 */
	
	public long createNewUser(Person user) {
		long id = -1;
		EstablishDBConnection();
		try {
	        Statement stm = dbconnection.createStatement();
	        String sql ="INSERT into " + KEY_TABLE_USER + " (" + KEY_TABLE_USER_USERNAME + ", " + KEY_TABLE_USER_PASSWORD + ", " + KEY_TABLE_USER_FIRSTNAME + ", " + KEY_TABLE_USER_LASTNAME + ", " + KEY_TABLE_USER_EMAIL + ") VALUES ('" +user.getNick()+ "','" +user.getPassword()+ "','" +user.getVorname()+ "','" +user.getNachname()+ "','" +user.getEmail()+ "')";
	        System.out.println("SQL Query: "+sql);
	        int r = stm.executeUpdate (sql, Statement.RETURN_GENERATED_KEYS);
	        ResultSet result = stm.getGeneratedKeys();
	        if (result.next()) {
	        	id = result.getLong(1);
	        	System.out.println("ID des angelegten User: " + String.valueOf(id));
	        }
	        else {
	        	System.out.println("Fehler beim angelegen des Users: " + String.valueOf(id));
	        }
	        System.out.println("Anzahl der angelegten User: "+String.valueOf(r));			
		} catch(Exception e) {
			System.out.println("Exception " + e);
		}
		finally {
			closeDBConnection();
		}
		closeDBConnection();
		return id;
	}
	
	public boolean editUser(Person user) {
		boolean success = true;
		EstablishDBConnection();
		try {
			// TODO: Prepared Statement
			Statement stm = dbconnection.createStatement();
			String sql ="SELECT count(*) FROM " + KEY_TABLE_USER + " WHERE " + KEY_TABLE_USER_USERNAME + " = '" + user.getNick() + "'";
			System.out.println("Statement: " + sql);
			ResultSet result = stm.executeQuery(sql);
			result.next();
			long rows = result.getLong(1);
			System.out.println("editUser(String username): Anzahl der gefundenen User: " + rows);
			if (rows == 1) {
				sql ="UPDATE " + KEY_TABLE_USER + " SET "
				 + KEY_TABLE_USER_FIRSTNAME + "='" + user.getVorname() + "', "
				 + KEY_TABLE_USER_LASTNAME + "='" + user.getNachname() + "', "
				 + KEY_TABLE_USER_ISCONFIRMED + "='" + (user.isConfirmed() ? 1: 0) + "', "
				 + KEY_TABLE_USER_ISADMIN + "='" + (user.isAdmin() ? 1: 0) + "', "
				 + KEY_TABLE_USER_PASSWORD + "='" + user.getPassword() + "', "
				 + KEY_TABLE_USER_EMAIL + "='" + user.getEmail() + "' "
				 + "WHERE " + KEY_TABLE_USER_USERNAME + " = '" + user.getNick() + "'";
				System.out.println("Statement: " + sql);
				int r = stm.executeUpdate(sql);
			}
			else {
				success = false;
				System.out.println("User " + user.getNick() + " nicht gefunden!");
			}
		}
		catch (Exception e) {
	          System.out.println("Exception " + e);
	          success = false;
	    }
		finally {
			closeDBConnection();
		}
		closeDBConnection();
		return success;	
	}
	
	public boolean confirmUser(String username) {
		boolean success = true;
		EstablishDBConnection();
		try {
			// TODO: Prepared Statement
			Statement stm = dbconnection.createStatement();
			String sql ="SELECT count(*) FROM " + KEY_TABLE_USER + " WHERE " + KEY_TABLE_USER_USERNAME + " = '" + username + "'";
			ResultSet result = stm.executeQuery(sql);
			result.next();
			long rows = result.getLong(1);
			System.out.println("confirmUser(String username): Anzahl der gefundenen User: " + rows);
			if (rows == 1) {
				sql ="UPDATE " + KEY_TABLE_USER + " SET " + KEY_TABLE_USER_ISCONFIRMED + "=1 " + " WHERE " + KEY_TABLE_USER_USERNAME + " = '" + username + "'";
				int r = stm.executeUpdate(sql);
			}
			else {
				success = false;
				System.out.println("User " + username + " nicht gefunden!");
			}
		}
		catch (Exception e) {
	          System.out.println("Exception " + e);
	          success = false;
	    }
		finally {
			closeDBConnection();
		}
		closeDBConnection();
		return success;
	}
	
	public boolean setAdmin(String username) {
		boolean success = true;
		EstablishDBConnection();
		try {
			// TODO: Prepared Statement
			Statement stm = dbconnection.createStatement();
			String sql ="SELECT count(*) FROM " + KEY_TABLE_USER + " WHERE " + KEY_TABLE_USER_USERNAME + " = '" + username + "'";
			ResultSet result = stm.executeQuery(sql);
			result.next();
			long rows = result.getLong(1);
			System.out.println("setAdmin(String username): Anzahl der gefundenen User: " + rows);
			if (rows == 1) {
				sql ="UPDATE " + KEY_TABLE_USER + " SET " + KEY_TABLE_USER_ISADMIN + "=1 " + " WHERE " + KEY_TABLE_USER_USERNAME + " = '" + username + "'";
				int r = stm.executeUpdate(sql);
			}
			else {
				success = false;
				System.out.println("User " + username + " nicht gefunden!");
			}
		}
		catch (Exception e) {
	          System.out.println("Exception " + e);
	          success = false;
	    }
		finally {
			closeDBConnection();
		}
		closeDBConnection();
		return success;
	}
	
	public boolean changePassword(String username, String password) {
		boolean success = true;
		EstablishDBConnection();
		try {
			// TODO: Prepared Statement
			Statement stm = dbconnection.createStatement();
			String sql ="SELECT count(*) FROM " + KEY_TABLE_USER + " WHERE " + KEY_TABLE_USER_USERNAME + " = '" + username + "'";
			ResultSet result = stm.executeQuery(sql);
			result.next();
			long rows = result.getLong(1);
			System.out.println("changePassword(String username, String password): Anzahl der gefundenen User: " + rows);
			if (rows == 1) {
				sql ="UPDATE " + KEY_TABLE_USER + " SET " + KEY_TABLE_USER_PASSWORD + "='" + password + "'" + " WHERE " + KEY_TABLE_USER_USERNAME + " = '" + username + "'";
				int r = stm.executeUpdate(sql);
			}
			else {
				success = false;
				System.out.println("User " + username + " nicht gefunden!");
			}
		}
		catch (Exception e) {
	          System.out.println("Exception " + e);
	          success = false;
	    }
		finally {
			closeDBConnection();
		}
		closeDBConnection();
		return success;
	}
	
	/*
	 * @return 
	 * 		true when username does exist in database
	 * 		false when username does NOT exist in database
	 */
	public boolean existsUser(String username) {
		EstablishDBConnection();
		boolean exist = true;
		try {
			// TODO: Prepared Statement
			Statement stm = dbconnection.createStatement();
			String sql ="SELECT count(*) FROM " + KEY_TABLE_USER + " WHERE " + KEY_TABLE_USER_USERNAME + " = '" + username + "'";
			ResultSet result = stm.executeQuery(sql);
			result.next();
			long rows = result.getLong(1);
			System.out.println("existsUser(String username): Anzahl der gefundenen User: " + rows);
			if (rows == 1) {
				exist = true;
				System.out.println("User mit Username " + username + " bereits in der Datenbank vorhanden!");
			}
			else {
				exist = false;
				System.out.println("User " + username + " nicht gefunden!");
			}
		}
		catch (Exception e) {
	          System.out.println("Exception " + e);
	    }		
		finally {
			closeDBConnection();
		}
		closeDBConnection();
		return exist;
	}
	
	
	/*
	 * @param username: username from user to be deleted in the database
	 * @return
	 * 		true when delete user succeeded
	 * 		false when delete user failed
	 */
	public boolean deleteUser (String username) {
		boolean exist = this.existsUser(username);
		boolean success = false;
		EstablishDBConnection();
		try {
			if (exist) {
				String sql ="DELETE FROM " + KEY_TABLE_USER + " WHERE " + KEY_TABLE_USER_USERNAME + " = '" + username + "'";
				Statement stm = dbconnection.createStatement();
				int r = stm.executeUpdate(sql);
				success = true;
			}
			else {
				success = false;
				System.out.println("User " + username + " nicht gefunden!");
			}
		}
		catch (Exception e) {
	          System.out.println("Exception " + e);
	          success = false;
	    }
		finally {
			closeDBConnection();
		}
		closeDBConnection();
		return success;
	}
	
	public boolean checkLoginUser (String username, String password) {
		boolean exist = this.existsUser(username);
		boolean credentialsOK = false;
		String password_db = "";
		EstablishDBConnection();
		if (exist) {		
			try {
				String sql ="SELECT " + KEY_TABLE_USER_PASSWORD + " FROM " + KEY_TABLE_USER + " WHERE " + KEY_TABLE_USER_USERNAME + " = '" + username + "'";
				Statement stm = dbconnection.createStatement();
				ResultSet result = stm.executeQuery(sql);
		          while(result.next() == true) {
		        	  password_db = result.getString(KEY_TABLE_USER_PASSWORD);
		        	  System.out.println("Passwort von user " + username + " in der Datenbank: " + password_db);
		        	  System.out.println("Übergebenes Passwort von user " + username + ": " + password);
		        	  if (password_db.equals(password)) {
		        		  credentialsOK = true;
		        	  }
		        	  else {
		        		  credentialsOK = false;
		        	  }
		          }				
			}
			catch (Exception e) {
				System.out.println("Exception " + e);
			}
			finally {
				closeDBConnection();
			}
		}
		closeDBConnection();
		return credentialsOK;
	}
	
/****************************************** Image Methods ******************************************/
	
	public boolean createImage(Image image, String username) {
		EstablishDBConnection();
		try {
	        Statement stm = dbconnection.createStatement();
	        String sql ="INSERT into " + KEY_TABLE_IMAGE + " (" + KEY_TABLE_IMAGE_NAME + ", " + KEY_TABLE_IMAGE_FILENAME + ", " + KEY_TABLE_IMAGE_PATH + ", " + KEY_TABLE_IMAGE_DATE + ", " + KEY_TABLE_IMAGE_USERNAME +") VALUES ('" + image.getName() + "','" + image.getFilename() + "','" + image.getPath() + "','" + image.getDate() + "','" + username + "')";
	        System.out.println("SQL Query: "+sql);
	        int r = stm.executeUpdate (sql);
	        System.out.println("Anzahl der angelegten Images: "+String.valueOf(r));
		} catch(Exception e) {
			System.out.println("Exception " + e);
		}
		finally {
			closeDBConnection();
		}
		closeDBConnection();
		return true;
	}
	
	public void deleteImage(String name) {
		EstablishDBConnection();
		try {
	        Statement stm = dbconnection.createStatement();
	        String sql ="DELETE from " + KEY_TABLE_IMAGE + " WHERE " + KEY_TABLE_IMAGE_NAME + " ='" + name + "'";
	        System.out.println("SQL Query Image löschen: "+sql);
	        int r = stm.executeUpdate (sql);
	        System.out.println("Anzahl der gelöschten Images: "+String.valueOf(r));
		}
		catch(Exception e) {
			System.out.println("Exception " + e);
		}
		finally {
			closeDBConnection();
		}
		closeDBConnection();
	}
	
	public boolean deleteAllImagesFromUser (String username) {
		EstablishDBConnection();
		boolean success = false;
		try {
	        Statement stm = dbconnection.createStatement();
	        String sql ="DELETE from " + KEY_TABLE_IMAGE + " WHERE " + KEY_TABLE_IMAGE_USERNAME + " ='" + username + "'";
	        System.out.println("SQL Query deleteAllImagesFromUser: "+sql);
	        int r = stm.executeUpdate (sql);
	        System.out.println("Anzahl der gelöschten Images: "+String.valueOf(r));
	        success = true;
		}
		catch(Exception e) {
			System.out.println("Exception " + e);
		}
		finally {
			closeDBConnection();
		}
		closeDBConnection();
		return success;
	}
	
	public boolean existsImage(String imagename) {
		EstablishDBConnection();
		try {
			Statement stm = dbconnection.createStatement();
			String sql ="SELECT count(*) FROM " + KEY_TABLE_IMAGE + " WHERE " + KEY_TABLE_IMAGE_NAME + " = '" + imagename + "'";
			//String sql ="SELECT * FROM " + KEY_TABLE_IMAGE + " WHERE " + KEY_TABLE_IMAGE_NAME + " = '" + imagename + "'";
			System.out.println("Abfrage, ob Image existiert: " + sql);
			ResultSet result = stm.executeQuery(sql);
			result.next();
			long rows = result.getLong(1);
			System.out.println("result.getLong(1)");
			System.out.println(result.getLong(1));
			/*
			int count = 0;
	        while(result.next() == true) {
	        	count++;
	        }
	        
	        System.out.println(count);
	        */
			return (rows==0)?false:true;
		}
		catch (Exception e) {
	          System.out.println("Exception " + e);
	    }
		finally {
			closeDBConnection();
		}
		closeDBConnection();
		return true;
	}
	
	
	public Image getImageFromImagename(String imagename) {
		boolean exists = this.existsImage(imagename);
		Image helpimage = new Image();
		EstablishDBConnection();
		if (exists) {		
			try {
				Statement stm = dbconnection.createStatement();
				String sql ="SELECT * FROM " + KEY_TABLE_IMAGE + " WHERE " + KEY_TABLE_IMAGE_NAME + " = '" + imagename + "'";
				System.out.println("Liefert Image mit dem Namen " + imagename + ": " + sql);
				ResultSet result = stm.executeQuery(sql);
				while(result.next() == true) {
					helpimage.setID(result.getLong(KEY_TABLE_IMAGE_ID));
					helpimage.setName(result.getString(KEY_TABLE_IMAGE_NAME));
					helpimage.setFilename(result.getString(KEY_TABLE_IMAGE_FILENAME));
					helpimage.setPath(result.getString(KEY_TABLE_IMAGE_PATH));
					helpimage.setDate(result.getDate(KEY_TABLE_IMAGE_DATE));
					helpimage.setNick(result.getString(KEY_TABLE_IMAGE_USERNAME));
		          }

			}
			catch (Exception e) {
		          System.out.println("Exception " + e);
		    }
			finally {
				closeDBConnection();
			}
		}
		closeDBConnection();
		return helpimage;
	}

	
	public ArrayList<Image> getAllImages() {
		EstablishDBConnection();
		ArrayList<Image> imagearray = new ArrayList();
		try {
			Statement stm = dbconnection.createStatement();
			String sql ="SELECT * FROM " + KEY_TABLE_IMAGE;
			ResultSet result = stm.executeQuery(sql);
	          while(result.next() == true) {
	        	  Image helpimage = new Image();
	        	  helpimage.setID(result.getLong(KEY_TABLE_IMAGE_ID));
	        	  helpimage.setName(result.getString(KEY_TABLE_IMAGE_NAME));
	        	  helpimage.setFilename(result.getString(KEY_TABLE_IMAGE_FILENAME));
	        	  helpimage.setPath(result.getString(KEY_TABLE_IMAGE_PATH));
	        	  helpimage.setDate(result.getDate(KEY_TABLE_IMAGE_DATE));
	        	  helpimage.setNick(result.getString(KEY_TABLE_IMAGE_USERNAME));
	        	  imagearray.add(helpimage);
	          }
		}
		catch (Exception e) {
	          System.out.println("Exception " + e);
	    }
		finally {
			closeDBConnection();
		}
		closeDBConnection();
		return imagearray;
	}
	
	public ArrayList<Image> getImagesFromUser(String username) {
		EstablishDBConnection();
		ArrayList<Image> imagearray = new ArrayList<Image>();
		try {
			Statement stm = dbconnection.createStatement();
			String sql ="SELECT * FROM " + KEY_TABLE_IMAGE + " WHERE " + KEY_TABLE_IMAGE_USERNAME + " ='" + username + "'";
			System.out.println("Abfrage, die alle Images eines Users liefert: " + sql);
			ResultSet result = stm.executeQuery(sql);
	          while(result.next() == true) {
	        	  Image helpimage = new Image();
	        	  helpimage.setID(result.getLong(KEY_TABLE_IMAGE_ID));
	        	  helpimage.setName(result.getString(KEY_TABLE_IMAGE_NAME));
	        	  helpimage.setFilename(result.getString(KEY_TABLE_IMAGE_FILENAME));
	        	  helpimage.setPath(result.getString(KEY_TABLE_IMAGE_PATH));
	        	  helpimage.setDate(result.getDate(KEY_TABLE_IMAGE_DATE));
	        	  helpimage.setNick(result.getString(KEY_TABLE_IMAGE_USERNAME));
	        	  imagearray.add(helpimage);
	          }
		}
		catch (Exception e) {
	          System.out.println("Exception " + e);
	    }
		finally {
			closeDBConnection();
		}
		closeDBConnection();
		return imagearray;
	}
	
}
